package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IAgencyPersonRestrictDao;
import com.boss.salary.entity.AgencyPersonRestrict;
import com.boss.sys.ca.user.dto.UserDTO;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class AgencyPersonRestrictDaoImpl extends BaseDaoImpl implements IAgencyPersonRestrictDao {

    /**
     * 新增
     *
     * @param agencyPersonRestrict
     */
    @Override
    public void insertAgencyPersonRestrict(AgencyPersonRestrict agencyPersonRestrict) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_PERSON_RESTRICT);
        sql.append("(id, create_time, update_time, agency_id, agency_code,")
                .append("agency_name, mof_div_code, merit_pay_id, merit_pay_name, merit_pay_code,")
                .append("person_num, is_deleted)")
                .append(" values (?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                agencyPersonRestrict.getId(), agencyPersonRestrict.getCreateTime(), agencyPersonRestrict.getUpdateTime(),
                agencyPersonRestrict.getAgencyId(), agencyPersonRestrict.getAgencyCode(), agencyPersonRestrict.getAgencyName(),
                agencyPersonRestrict.getMofDivCode(), agencyPersonRestrict.getMeritPayId(), agencyPersonRestrict.getMeritPayName(),
                agencyPersonRestrict.getMeritPayCode(), agencyPersonRestrict.getPersonNum(), agencyPersonRestrict.getIsDeleted()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    /**
     * 删除
     *
     * @param id
     */
    @Override
    public void deleteAgencyPersonRestrict(String id, String mofDivCode) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_PERSON_RESTRICT);
        sql.append(" set is_deleted='1' ")
                .append("where id=? and mof_div_code=? ");
        Object[] params = new Object[]{
                id, mofDivCode
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    /**
     * 修改
     *
     * @param agencyPersonRestrict
     */

    @Override
    public void updateAgencyPersonRestrict(AgencyPersonRestrict agencyPersonRestrict) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_PERSON_RESTRICT);
        sql.append(" set  update_time = ?, agency_id = ?, agency_code = ?,")
                .append("agency_name = ?, mof_div_code = ?, merit_pay_id = ?, merit_pay_name = ?, merit_pay_code = ?,")
                .append("person_num = ? WHERE id = ?");


        Object[] params = new Object[]{
                agencyPersonRestrict.getUpdateTime(), agencyPersonRestrict.getAgencyId(), agencyPersonRestrict.getAgencyCode(),
                agencyPersonRestrict.getAgencyName(), agencyPersonRestrict.getMofDivCode(), agencyPersonRestrict.getMeritPayId(),
                agencyPersonRestrict.getMeritPayName(), agencyPersonRestrict.getMeritPayCode(), agencyPersonRestrict.getPersonNum(),
                agencyPersonRestrict.getId()
        };
        this.getjdbcTemplate().update(sql.toString(), params);
    }


    /**
     *  查询
     *  可以通过
     *  childrenId 父id 查询
     *  state 状 态  0 全部 1 设置 2 未设置 查询
     * @param paginationDTO
     */

    @Override
    public List<Map<String, Object>> queryAgencyPersonRestrictPage(PaginationDTO paginationDTO ,String mofDivCode,List<String> childrenId,String state) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append("select  count(c.id) \"id\",b.agency_code AS \"agencyCode\",b.agency_name AS \"agencyName\",b.id AS \"agencyId\",b.mof_div_code as \"mofDivCode\" ")
                .append(" from ")
                .append(TableName.AGENCY_INFO).append(" b")
                .append(" LEFT JOIN ")
                .append(TableName.AGENCY_PERSON_RESTRICT).append(" c ")
                .append(" on b.id =c.agency_id  ")
                .append(" where  b.is_leaf='1' and b.is_deleted=0 ")
                .append(" and b.mof_div_code =?  ");
                 paramList.add(mofDivCode);
        if(childrenId.size()>0){
            sql.append(" and ").append(SysUtils.inSql("b.id", childrenId));
        }
        if(state.equals("1")){
            sql.append(" and  c.id  is not null "   );
        }
        if(state.equals("2")){
            sql.append(" and  c.id  is null "   );
        }
        sql.append(" GROUP BY b.agency_code ,b.agency_name ,b.id ,b.mof_div_code ");
        sql.append(" ORDER BY b.agency_code ");
        String querySql = this.boxUpSql(sql.toString());
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }

    /**
     * 获取数据总数
     *  可以通过
     *  childrenId 父id 获取条数
     *  state 状 态  0 全部 1 设置 2 未设置 获取条数
     * @return
     */

    @Override
    public int queryAgencyPersonRestrict(String mofDivCode,List<String> childrenId,String state) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append("select count(*) ")
                .append(" from ")
                .append(TableName.AGENCY_INFO).append(" b")
                .append(" LEFT JOIN ")
                .append(" (select DISTINCT agency_id from ")
                .append(TableName.AGENCY_PERSON_RESTRICT).append(") c ")
                .append(" on b.id =c.agency_id  ")
                .append(" where  b.is_leaf='1' and b.is_deleted=0 ")
                .append(" and b.mof_div_code =?  ");
        paramList.add(mofDivCode);
        if(childrenId.size()>0){
            sql.append(" and ").append(SysUtils.inSql("b.id", childrenId));
        }
        if(state.equals("1")){
            sql.append(" and  c.agency_id  is not null "   );
        }
        if(state.equals("2")){
            sql.append(" and  c.agency_id  is null "   );
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class,paramList.toArray());
    }

    /**
     * 根据id 插入
     */

    @Override
    public void insertAgencyPersonRestricts(List<AgencyPersonRestrict> agencyPersonRestrictList) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_PERSON_RESTRICT);
        sql.append("( id, create_time, agency_id, agency_code,")
                .append("agency_name, mof_div_code, merit_pay_id, merit_pay_name, merit_pay_code,")
                .append("person_num, is_deleted)")
                .append(" VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramsList = new ArrayList<>(agencyPersonRestrictList.size());
        for (AgencyPersonRestrict agencyPersonRestrict : agencyPersonRestrictList) {
            Object[] params = new Object[]{
                    agencyPersonRestrict.getId(),agencyPersonRestrict.getCreateTime(),
                    agencyPersonRestrict.getAgencyId(), agencyPersonRestrict.getAgencyCode(), agencyPersonRestrict.getAgencyName(),
                    agencyPersonRestrict.getMofDivCode(), agencyPersonRestrict.getMeritPayId(), agencyPersonRestrict.getMeritPayName(),
                    agencyPersonRestrict.getMeritPayCode(), agencyPersonRestrict.getPersonNum(), agencyPersonRestrict.getIsDeleted()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 11; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }


    /**
     * 根据id 修改
     *
     * @param agencyPersonRestrictList
     */

    @Override
    public void updateAgencyPersonRestricts(List<AgencyPersonRestrict> agencyPersonRestrictList) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_PERSON_RESTRICT);
        sql.append(" set  update_time=?, agency_id=?, agency_code=?,")
                .append("agency_name=?, mof_div_code=?, merit_pay_id=?, merit_pay_name=?, merit_pay_code=?,")
                .append("person_num=?");
        sql.append(" where id = ?");
        List<Object[]> paramsList = new ArrayList<>();

        for (AgencyPersonRestrict agencyPersonRestrict : agencyPersonRestrictList) {
            paramsList.add(new Object[]{
                    agencyPersonRestrict.getUpdateTime(), agencyPersonRestrict.getAgencyId(), agencyPersonRestrict.getAgencyCode(),
                    agencyPersonRestrict.getAgencyName(), agencyPersonRestrict.getMofDivCode(), agencyPersonRestrict.getMeritPayId(), agencyPersonRestrict.getMeritPayName(),
                    agencyPersonRestrict.getMeritPayCode(), agencyPersonRestrict.getPersonNum(), agencyPersonRestrict.getId()
            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(),   new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 10; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    /**
     * 根据agencyid 查这个单位下的所有人数以及状态
     *
     * @param userDTO
     * @param agencyId
     * @return
     */
    @Override
    public List<AgencyPersonRestrict> queryAgencyIdList(String agencyId, UserDTO userDTO) {
        StringBuilder sql = new StringBuilder();
        sql.append("select  id,mof_div_code, merit_pay_id, merit_pay_name,")
                .append("merit_pay_code, person_num ,agency_id,agency_code,agency_name from ")
                .append(TableName.AGENCY_PERSON_RESTRICT);
        sql.append(" where agency_id=? and mof_div_code=? ");
        Object[] params = new Object[]{
                agencyId, userDTO.getProvince()
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyPersonRestrict.class), params);

    }

    /**
     * /**
     * 根据区划 查询工资类型
     * @param
     * @param mofDivCode
     * @return
     */
    @Override
    public List<AgencyPersonRestrict> agencyType(String mofDivCode) {
        StringBuilder sql = new StringBuilder();
        sql.append("select  b.id as \"meritPayId\" , b.code as \"meritPayCode\" ,b.name as \"meritPayName\"  from ")
                .append(TableName.BASE_DATA).append(" b");
        sql.append(" where b.type='salary_type' and b.is_deleted=0 ");
        sql.append(" and  b.mof_div_code=?  ");
        Object[] params = new Object[]{
                mofDivCode
        };
       // String querySql = this.boxUpSql(sql.toString());
       return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyPersonRestrict.class), params);
    }

    /**
     * 根据机构id查询当前单位
     *
     * @param userDTO
     * @param agencyId
     * @return
     */
    @Override
    public List<AgencyPersonRestrict> currentUnitList(String agencyId, UserDTO userDTO) {
        StringBuilder sql = new StringBuilder();
        sql.append("select  a.id as \"agencyId\",a.agency_code AS \"agencyCode\",a.agency_name as \"agencyName\", a.mof_div_code as \"mofDivCode\",")
                .append("b.id as meritPayId, b.code as meritPayCode ,b.name as meritPayName from ")
                .append(TableName.AGENCY_INFO).append(" a ")
                .append(" LEFT JOIN  ").append(TableName.BASE_DATA).append(" b ")
                .append("  on a.mof_div_code=b.mof_div_code ");
        sql.append(" where a.agency_id=? and a.mof_div_code=? ");
        Object[] params = new Object[]{
                agencyId, userDTO.getProvince()
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyPersonRestrict.class), params);

    }

    /**
     * 查询工资类型
     * @param userDTO
     * @return
     */
    @Override
    public List<AgencyPersonRestrict> selectWageType(UserDTO userDTO) {

        StringBuilder sql = new StringBuilder();
        sql.append("select  id as \"meritPayId\" ,name as \"meritPayName\" ,code as \"meritPayCode\"")
                .append(" from ").append(TableName.BASE_DATA)
                .append(" where mof_div_code =?");
        Object[] params = new Object[]{
                userDTO.getProvince()
        };
        return this.getjdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper(AgencyPersonRestrict.class),params);

    }

    /**
     * 查询人员编制数表中人员限制数
     * @param agencyId
     * @param payCode
     * @return
     */
    @Override
    public Integer queryCurrentNumber(String agencyId, String payCode) {
        StringBuilder sql = new StringBuilder();
        sql.append("select person_num  from ")
                .append( TableName.AGENCY_PERSON_RESTRICT);
        sql.append(" where agency_id=? and merit_pay_code=?");
        Object[] params = new Object[]{
                agencyId,payCode
        };
        try {
            return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, params);
        }catch (Exception e){
            logger.info("未查到数据或查询到多条",e);
            return 0;
        }

    }
}
